Joins
Pyramid determines the table relationships (joins) based on heuristics that define the first column in each table as the primary key column. It then finds the primary key column in each table, and looks for tables that have the same column as a foreign key. The algorithm used to create joins can be changed or disabled if needed.
Join Types
- Full Outer Join: returns all rows from both tables
- Inner Join: returns all rows from both tables based on a matching column. Only returns rows where the matching column's values are identical.
- Left Outer Join: returns all rows from the first/ left table, and matching rows from the second/ right table.
- Right Outer Join: returns all rows from the second/ right table, and matching rows from the first/ left table.
How Joins are Defined
If the default algorithm doesn't suit your data model, you can choose a different auto-relationship to create joins based on table or column names. Alternatively, you can disable auto-relationships and add all the joins manually.
Note: by definition, primary key columns contain unique values; each row of a primary key columns contains a unique identifier. If a column that was set as a primary key contains any duplicate rows, you will receive a warning after the model is processed
- Click here to learn more about auto-relationships.
Adding and Editing Joins
New joins can be added from a given column in a table, to a selected column in another table. Joins can be edited from the Join context menus, and from a selected join's Properties panel.
Editing joins can involve changing the join type, changing or adding join key columns, setting a join key condition, changing the join key operation, the join direction, or making a join bidirectional.
- Click here to learn about join keys.
Editing Relationships
Table relationships can be edited by changing existing joins, adding new joins, or deleting joins.
Change Join Type
Change the join type of an existing join by clicking on it. This will open both the join type context menu (red highlight below), and the Properties panel (green highlight). You can change the join type from either location.
Add Joins
Add a join by right clicking on the column belonging to the owner side. From Add Relationship, select the table and column to which you want to the join to connect.
Delete Joins
Right click on the join and click 'Delete' from the context menu (red highlight below).
Change Join Direction
The join direction can be changed for any join, both from the join context menu (red highlight below), or from the join's Properties panel (left click on the join to open its Properties panel).
Validate Join
Ensure that the join relationship is valid. This can be done from the:
- Join context menu (red highlight below).
- Join Properties panel (left click on the join to open its Properties panel).
- From the ribbon (select the preview size and validate all joins in the model for the selected number of rows; invalid joins will be shown in yellow in the join diagram).
- Click here to learn more about validating joins.
Copy Join
Click Copy to copy the script that underlies this join to your clipboard. Tip: You can use this script when testing in the database.
Join Properties
Join Type
Change the join type from the 'Join Type' drop-down list (green highlight below).
Bidirectional
Joins can be one-way or two-way (bidirectional). The most common type is one-way, and this is the default in Pyramid. You can make a bidirectional join by enabling the 'Bidirectional' option from the join's Properties (red highlight below).
- Click here to learn about bidirectional joins.
Many to Many
When the relationship between the primary and foreign tables is "one-to-many" and doesn't validate due to duplications of unique keys it will replace the relationship to a many-to-many relation and as a result will be a valid connection (the user should understand his data and understand that it can be a many to many relationships).
Add or Edit Join Keys
- Add join keys by clicking 'New Join Key' (blue highlight below) and selecting the required columns.
- Change the join key columns by selecting different columns from the drop-downs (purple highlight below).
- Insert an And/ Or condition between multiple join keys.
- Change the join direction by clicking the double arrows icon (yellow highlight below).
- Change the join key operator (white arrow below).
- Click here to learn more about join keys.
Validate
Click the Validate button to test the relationship (orange highlight below). If the relationship is valid, you'll receive a green confirmation message. If the relationship is not valid, you'll receive an orange alert with an explanation of the issues found:
- Click here to learn more about validating joins.